﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using prototype123.controllers;
using prototype123.models;

namespace prototype123.data_access
{
    //This class handles all data access between the application and the
    //studentterm table in the database.
    class StudentTermDAO
    {

        private SqlConnection db_connection; // = prototype123.Program.connect.GetConnection();

        public StudentTermDAO(SqlConnection connection)
        {
            db_connection = connection;
        }

        /*precondition: the system needs to get the current term that a student is enrolled in
         description: given the student's ID number (stuid), this method queries the database to
               get the term number that the student is currently in. This is done by querying for
               the MAX of that student's term numbers, as the max is the most recent term.
         postcondition: the system receives an integer representing the current term number
                of the student represented by int stuid */
        /// <precondition>System needs the current term that a student is enrolled in</precondition>
        /// <summary>
        /// Gets the ID number of the current term for a student
        /// </summary>
        /// <param name="stuid">ID# of the current student</param>
        /// <returns>Term ID# of the current term the student is enrolled in</returns>
        /// <postcondition>System now has the current term ID for the current student</postcondition>
        public int GetCurrentStudentTerm(int stuid)
        {
            SqlCommand id = new SqlCommand("SELECT MAX(TERM_ID) FROM STU_TERM WHERE STU_ID = " + stuid, db_connection);
            SqlDataReader read = id.ExecuteReader();
            read.Read();

            if (read.HasRows == false)
            {
                read.Close();
                return -1;
            }

            int result = Convert.ToInt32(read[0].ToString());
            read.Close();
            return result;
        }

        /// <precondition>Educator has logged in and student is selected</precondition>
        /// <summary>
        /// Ends the current term that a student is enrolled in
        /// </summary>
        /// <param name="stuid">ID# of the student whose term we are ending</param>
        /// <param name="termid">The term that we are ending - the current term</param>
        /// <param name="date">The exact date and time that the term is officially ended</param>
        /// <postcondition>Educator has ended term for a student</postcondition>
        public void EndTerm(int stuid, int termid, DateTime date)
        {
            SqlCommand endterm = new SqlCommand("UPDATE STU_TERM SET STATUS = 'S', END_DATE = '" +
                date + "' WHERE STU_ID = " + stuid + " and TERM_ID = " + termid, db_connection);

            endterm.ExecuteNonQuery();            
        }

        /// <precondition>Educator is logged in and is attempting to end the term</precondition>
        /// <summary>
        /// Checks to see how many instructional days are required for a given student term.
        /// </summary>
        /// <param name="stuid">The ID# of the student who is taking the class</param>
        /// <param name="termid">The term in which the student is enrolled.</param>
        /// <returns>The number of instructional days required for the student's term</returns>
        /// <postcondition>System is able to confirm/deny that the needed days has been met</postcondition>
        public int GetDaysNeeded(int stuid, int termid)
        {
            SqlCommand getdays = new SqlCommand("SELECT NUM_DAYS FROM STU_TERM WHERE STU_ID = " +
                stuid + " and TERM_ID = " + termid, db_connection);
            SqlDataReader read = getdays.ExecuteReader();
            read.Read();
            int days = Convert.ToInt32(read["NUM_DAYS"].ToString());
            read.Close();
            return days;
        }

        /// <summary>
        /// Creates a new student term for a given student in the database
        /// </summary>
        /// <param name="stuid">The students id</param>
        /// <param name="termid">The term id</param>
        /// <param name="numdays">the number of days</param>
        public void NewStudentTerm(int stuid, int termid, int numdays)
        {
            SqlCommand getdays = new SqlCommand("INSERT INTO STU_TERM (STU_ID, TERM_ID, START_DATE, NUM_DAYS, STATUS)  " + 
                "VALUES(" + stuid + ", " + termid + ", '" + DateTime.Now + "' , " + numdays + " , " + "'P')", db_connection);
            getdays.ExecuteNonQuery();
        }

    }
}
